<!DOCTYPE html>
<html>
<head>
    <title>09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./007.高级查询.html" class="">07.高级查询-排序 多表联合 聚合与分组 限制条数</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./008.多表连接查询.html" class="">08.多表连接查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./009.子查询 ANY ALL [NOT] IN [NOT]EXISTS.html" class="selected">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./010.事务-索引-视图.html" class="">010.事务-索引-视图</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./011.T-SQL.html" class="">011.T-SQL学习</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./012.存储过程.html" class="">012.存储过程</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./013.触发器.html" class="">10.触发器</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 09.子查询 ANY ALL [NOT] IN [NOT]EXISTS </h1>
            <div class="doc_post"> <p class="md_block">

<div class="toc"><ul>
<li>
<a href="#toc_0">09.子查询 ANY ALL [NOT] IN [NOT]EXISTS</a>
<ul>
<li>
<a href="#toc_1">普通查询与子查询　ANY  | ALL</a>
</li>
<li>
<a href="#toc_2">[NOT] IN子查询</a>
</li>
<li>
<a href="#toc_3">[NOT] EXISTS子查询</a>
</li>
<li>
<a href="#toc_4">在列上使用子查询</a>
</li>
</ul>
</li>
</ul>
</div>
</p>


<h2 id="toc_1">普通查询与子查询　ANY  | ALL</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 直接通过班级ID查询5班的姓名</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="o">=</span> <span class="mi">5</span><span class="p">;</span>

<span class="c1">-- 如果不知道5班的ID,而知道五班的名称,则通过子查询得到5再查询</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">where</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏五班&#39;</span><span class="p">);</span>


<span class="c1">-- 查询出5班与6班学生的姓名</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="o">=</span> <span class="mi">5</span> <span class="k">OR</span> <span class="n">classid</span> <span class="o">=</span> <span class="mi">6</span><span class="p">;</span>

<span class="c1">-- ANY(别名SOME)关键字.相当于条件判断中的OR(或者的意思)</span>
<span class="c1">-- ALL(相当于多条件判断中的AND,)</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="o">=</span> <span class="k">ANY</span><span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">where</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏五班&#39;</span><span class="k">or</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏六班&#39;</span><span class="p">);</span>

<span class="c1">-- 如下写法报错</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="o">=</span> <span class="k">ANY</span><span class="p">(</span><span class="s1">&#39;5&#39;</span><span class="p">,</span><span class="s1">&#39;6&#39;</span><span class="p">);</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_2">[NOT] IN子查询</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 以上查询,常用的子查询:IN子查询:就是通过一个集合[数组]作为条件去查询.</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="k">IN</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="mi">6</span><span class="p">);</span> <span class="c1">-- 本语句还不算是子查询</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="k">IN</span><span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">where</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏五班&#39;</span><span class="k">or</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏六班&#39;</span><span class="p">);</span>

<span class="c1">-- NOT IN  等价于 IN的取反</span>
<span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span>  <span class="k">NOT</span> <span class="k">IN</span><span class="p">(</span><span class="mi">5</span><span class="p">,</span><span class="mi">6</span><span class="p">);</span>


<span class="k">select</span> <span class="n">classid</span><span class="p">,</span><span class="k">count</span><span class="p">(</span><span class="n">classid</span><span class="p">)</span> <span class="k">from</span> <span class="n">kp_user_info</span> <span class="k">where</span> <span class="n">classid</span> <span class="o">&gt;=</span> <span class="k">ALL</span><span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">where</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏五班&#39;</span><span class="k">or</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏六班&#39;</span><span class="p">)</span> <span class="k">GROUP</span> <span class="k">by</span> <span class="n">classid</span><span class="p">;</span>
</pre></div>

<!--block_code_end-->
<blockquote>

<p class="md_block">
    <span class="md_line">注意 ANY|ALL作为条件时,可以在主查询语句中用 =,&lt;&gt;,&gt;,&lt;,&lt;=,&gt;=,&lt;=&gt;这么些运算符,而IN 或 NOT IN 则不可以</span>
</p>

</blockquote>

<h2 id="toc_3">[NOT] EXISTS子查询</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- EXISTS:只判断子查询有没有记录,如果有记录则条件为真,如果没有则条件为假</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">kp_user_info</span> <span class="k">WHERE</span> <span class="k">EXISTS</span><span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">where</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏五班&#39;</span><span class="p">);</span> <span class="c1">-- 得到整张表的行</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">kp_user_info</span> <span class="k">WHERE</span> <span class="k">EXISTS</span><span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span><span class="p">);</span> <span class="c1">-- 得到整张表的行</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">kp_user_info</span> <span class="k">WHERE</span> <span class="k">EXISTS</span><span class="p">(</span><span class="k">select</span> <span class="k">NULL</span><span class="p">);</span> <span class="c1">-- 得到整张表的行</span>

<span class="c1">-- 在子查询中一定要加入条件:父查询表的某列要与子询子表的某列绑字在子查询的条件上</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span> <span class="k">FROM</span> <span class="n">kp_user_info</span> <span class="k">WHERE</span> <span class="k">EXISTS</span><span class="p">(</span>
<span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">where</span> <span class="n">cname</span> <span class="o">=</span> <span class="s1">&#39;鲲鹏五班&#39;</span> <span class="k">AND</span> <span class="n">kp_user_info</span><span class="p">.</span><span class="n">classid</span> <span class="o">=</span> <span class="n">kp_class</span><span class="p">.</span><span class="n">id</span>
<span class="p">);</span>

<span class="c1">-- EXISTS  与  IN 的使用环境不同而效率不同.</span>
<span class="cm">/*</span>
<span class="cm">A表:主查询表</span>
<span class="cm">B表:子查询的表</span>
<span class="cm">当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.</span>
<span class="cm">如:A表有10000条记录,B表有1000000条记录,那么exists()子查询会执行10000次去判断A表中的id是否与B表中的id相等.</span>

<span class="cm">如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.</span>

<span class="cm">再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,</span>
<span class="cm">因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.</span>

<span class="cm">结论:exists()适合B表比A表数据大的情况</span>
<span class="cm">当A表数据与B表数据一样大时,in与exists效率差不多,可任选一个使用.</span>
<span class="cm">*/</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_4">在列上使用子查询</h2>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">-- 在要求的结果集中某列用子查询:注意绑定列</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span><span class="n">uname</span><span class="p">,</span><span class="n">classid</span><span class="p">,(</span><span class="k">select</span> <span class="n">cname</span> <span class="k">from</span> <span class="n">kp_class</span> <span class="k">WHERE</span> <span class="n">kp_user_info</span><span class="p">.</span><span class="n">classid</span> <span class="o">=</span> <span class="n">kp_class</span><span class="p">.</span><span class="n">id</span><span class="p">)</span> <span class="k">from</span> <span class="n">kp_user_info</span><span class="p">;</span>
</pre></div>

<!--block_code_end--> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>